MySQL 9.0 – it’s time to abandon the weak authentication method

With the latest MySQL Innovation Release, we decided that it was time to remove the remaining weak authentication plugin: mysql_native_password.

We previously deprecated it and made it not default loaded in MySQL 8.4 LTS and, now, in 9.0 it’s gone!

Reasons

Oracle places significant attention on the security of all its products, and MySQL is no exception. The removal of the weak authentication plugin has been carefully considered, we had some extra time for the LTS release as it was initially intended for version 8.4, but it is now fully effective.

But why is the mysql_native_password considered as weak compared to more modern authentication methods like the default caching_sha2_password:

  1. Weak Hashing Algorithm: mysql_native_password uses the SHA-1 hashing algorithm to hash passwords. SHA-1 is considered weak and vulnerable to certain types of cryptographic attacks, such as collision attacks, where two different inputs produce the same hash output (see SHAttered).
  2. No Salt: mysql_native_password does not use salting when hashing passwords. Salting adds random data to the password before hashing, which makes it more difficult for attackers to use precomputed hashes to crack passwords. The lack of salting makes this authentication method more vulnerable to such attacks.
  3. No Iterations: more secure hashing methods use multiple iterations of the hash function to slow down the hashing process, making brute-force attacks more time-consuming. mysql_native_password does not use multiple iterations, which makes it faster to compute and therefore easier to brute-force.

Implications

In practice, this means that old connectors that were already struggling with MySQL 8.0, like PHP 7.2 for example, won’t be able to connect to MySQL 9.0.

Let’s have a look at this simple PHP example:

<?php
$servername = "192.168.56.1";
$username = "test_user";
$password = "xxxxxxx";


echo "PHP version: " . phpversion() . "\n";

$conn = new mysqli($servername, $username, $password );

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error . "\n");
}
echo "Connected successfully\n";

echo "MySQL version: " . $conn->server_info . "\n";

$conn->close();
?>

The script is running on a fresh Oracle Linux 8 with PHP and php-mysqlnd installed:

[root@mysql1 ~]# cat /etc/oracle-release 
Oracle Linux Server release 8.6

[root@mysql1 ~]# rpm -qa | grep php
php-mysqlnd-7.2.24-1.module+el8.2.0+5510+6771133c.x86_64
php-cli-7.2.24-1.module+el8.2.0+5510+6771133c.x86_64
php-fpm-7.2.24-1.module+el8.2.0+5510+6771133c.x86_64
php-pdo-7.2.24-1.module+el8.2.0+5510+6771133c.x86_64
php-common-7.2.24-1.module+el8.2.0+5510+6771133c.x86_64
php-7.2.24-1.module+el8.2.0+5510+6771133c.x86_64

Now let’s run the script that connects to our MySQL 9.0 server where the test_user has been created:

SQL> select version();
+-----------+
| version() |
+-----------+
| 9.0.0     |
+-----------+
1 row in set (0.0005 sec)

SQL> select user, host, plugin from mysql.user where user='test_user';
+-----------+------+-----------------------+
| user      | host | plugin                |
+-----------+------+-----------------------+
| test_user | %    | caching_sha2_password |
+-----------+------+-----------------------+
1 row in set (0.0009 sec)
[root@mysql1 ~]# php test.php 
PHP version: 7.2.24
PHP Warning:  mysqli::__construct(): The server requested authentication method
 unknown to the client [caching_sha2_password] in /root/test.php on line 9
PHP Warning:  mysqli::__construct(): (HY000/2054): The server requested
 authentication method unknown to the client in /root/test.php on line 9
Connection failed: The server requested authentication method unknown to the client

Solutions

The only valid, supported, and recommended solution if you want to use MySQL 9 is to upgrade your connector. For this example, it will be required to upgrade to a more recent PHP.

A man who knows is worth two!

You can also continue to use MySQL 8. MySQL 8.4 is an LTS version that is still supported for many years (extended support will end in April 2032).

If you don’t use any support and if you run MySQL on your own, in the end, MySQL is Open Source and pluggable, you can write your own authentication plugin and why not backport the mysql_native_password one? This is not recommended of course as it won’t make it more secure but it’s feasible. Everything is (almost) feasible and that’s the reason why MySQL is cool.

This is an example:

Enjoy MySQL and enjoy secure connections to MySQL 9.0!

Extra

My friend Marco pointed me that mysql_native_password.so was still part of MySQL 9.0. And indeed it is, but this is the plugin for the client. There is no plugin for the server:

$ rpm -ql mysql-community-client-plugins-9.0.0-10.fc40.x86_64 | grep plugin/
/usr/lib64/mysql/plugin/authentication_kerberos_client.so
/usr/lib64/mysql/plugin/authentication_ldap_sasl_client.so
/usr/lib64/mysql/plugin/authentication_oci_client.so
/usr/lib64/mysql/plugin/authentication_webauthn_client.so
/usr/lib64/mysql/plugin/mysql_native_password.so

The plugin I used in the example, mysql_native_password_legacy.so, is a plugin I wrote to illustrate the example and it’s not available with MySQL.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

11 Comments

  1. How exactly certain types of cryptographic attacks, such as collision attacks make mysql_native_password weaker?

    What is the effect of salt on the connection speed? It means an extra round trip, is it not?

    • Hi Sergei,

      The problem in being able to get the hash (mysql_native_password could be also used without TLS/SSL) and being able to compare it with different data that could have the same hash makes it weak and problematic.
      About the salt, etc.. this is the reason why it’s also cached. The method caches the hashed credentials on the server-side to reduce the overhead of repeated hashing and salting operations for subsequent authentications, improving performance without compromising security.

      There is an interesting blog post from Percona also illustrating that: https://www.percona.com/blog/brute-force-mysql-password-from-a-hash/

  2. I think there’s some misunderstanding here. You always can get the hash and compare it with different data that _could_ have the same hash.

    To actually get the same hash one needs to find another password that hashes to the same string. As far as I know, this problem isn’t solved yet. But you’re right, one can generate two passwords with the same hash now. I just don’t understand how it makes mysql_native_password weaker and hoped you could clarify that.

    • Hi Sergei,

      Have you already tried to check the hash in MySQL of the same password ?

      SQL> create user sergei identified by ‘easypwd’;
      SQL> create user sergei2 identified by ‘easypwd’;
      SQL> show create user sergei\G
      *************************** 1. row ***************************
      CREATE USER for sergei@%: CREATE USER `sergei`@`%` IDENTIFIED WITH ‘caching_sha2_password’ AS 0x24412430303524673F4A2A3104495F58200B731D5E6A671D4A0A35705A58476F6C513166336C66662F45396A6738656845596A72777455724665667379494365375577776B2E REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
      1 row in set (0.0007 sec)

      SQL> show create user sergei2\G
      *************************** 1. row ***************************
      CREATE USER for sergei2@%: CREATE USER `sergei2`@`%` IDENTIFIED WITH ‘caching_sha2_password’ AS 0x24412430303524610B223977205D162B0F082E5274091A2E4805583564364B687A6D59713230566D495676564178714F3551426C51726439563038432F37657A315155514231 REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
      1 row in set (0.0007 sec)

      As you can see the passwords are the same but not the hash, which makes it more complicate to compare when using some brute generating programs.

      • Yes, this is your point 2. Salt. Salt is good, it makes hashes for the same password different. It does not complicate brute force, but it does make an attack with precomputed tables much more expensive or much less effective. I understand the benefits of salt.

        I don’t understand why SHA1 hash collisions make mysql_native_password weaker, so I was asking about that. Your point 1.

        • Both are bond together in fact, caching_sha2_password uses SHA-256, which is a more secure hashing algorithm compared to SHA-1. SHA-256 is part of the SHA-2 family of cryptographic hash functions and is widely regarded as secure against collision and preimage attacks, even if adding the salt also prevents from preimage attacks.
          So it’s a combination of all 3 together, adding salt, better hashing and multiple iterations (5000 iirc) that makes it much more robust.
          Additionally, SHA1 has vulnerabilities and is considered weak. NIST deprecated it in 2011.
          With mysql_native_password, if an attacker obtains the hash (from the mysql.user table or by sniffing an unencrypted channel), they can reverse engineer and crack these passwords quickly, especially if they are short (8 characters or less, which is common).
          I hope this replies to your concerns.
          Cheers,

          • Almost. Slower hash is good, many iterations are good, salt is good. In total, it’s good change to move from mysql_native_password to something modern.

            But “SHA1 is less secure for passwords” is purely FUD, NIST only disallowed it for digital signatures, where hash collisions are a problem. For passwords it is explicitly allowed. And it is impossible to obtain the hash by sniffing an unencrypted channel, because the client never sends the hash to the server.

          • Hi Sergei, you are right, but while SHA-1 was explicitly allowed for password hashing in MySQL due to historical reasons and its implementation in the challenge-response mechanism, it does have vulnerabilities by modern standards and Oracle’s security team was strict about removing it from MySQL. So even if the authentication process is designed to keep the password secure even over unencrypted channels and that’s the reason we could keep it in MySQL 8.4, updating to a stronger cryptographic method and ensuring encrypted connections was the request we had to comply with more modern security requirements.

  4. Fred a small comment… when using the Linux generic the plugin file name is:
    ll /opt/mysql_templates/mysql-9P/lib/plugin/*native*
    -rwxr-xr-x 1 mysql mysql 18352 Jun 7 13:20 /opt/mysql_templates/mysql-9P/lib/plugin/mysql_native_password.so. <—-

    • Ciao Marco,

      I’ve added an extra section, explaining that. the mysql_native_password_legacy.so is a plugin I wrote to illustrate the possibility for people to write their own non-recommended solution.

Leave a Reply

Your email address will not be published. Required fields are marked *

As MySQL Community Manager, I am an employee of Oracle and the views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

You can find articles I wrote on Oracle’s blog.

Continue